<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1" xml:lang="en">
  <title id="ih135496">Summary of Built-in Functions</title>
  <abstract>Greenplum Database supports built-in functions and operators including analytic
    functions and window functions that can be used in window expressions. For information about
    using built-in Greenplum Database functions see, "Using Functions and Operators" in the
      <cite>Greenplum Database Administrator Guide</cite>.</abstract>
  <body>
    <p>
      <ul id="ul_tpq_zwq_kr">
        <li>
          <xref href="#topic27" format="dita"/>
        </li>
        <li>
          <xref format="dita" href="#topic29" type="topic"/>
        </li>
        <li>
          <xref href="#topic_gn4_x3w_mq" format="dita"/></li>
        <li>
          <xref format="dita" href="#topic30" type="topic"/>
        </li>
        <li>
          <xref format="dita" href="#topic31" type="topic"/>
        </li>
        <li><xref href="#topic_vpj_ss1_lfb" format="dita"/></li>
        <li><xref href="#functions-range" format="dita"/></li>
      </ul>
    </p>
  </body>
  <topic id="topic27" xml:lang="en">
    <title id="in201560">Greenplum Database Function Types</title>
    <body>
      <p>Greenplum Database evaluates functions and operators used in SQL expressions. Some
        functions and operators are only allowed to run on the master since they could lead to
        inconsistencies in Greenplum Database segment instances. This table describes the Greenplum
        Database Function Types.</p>
      <table id="in201681">
        <title>Functions in Greenplum Database</title>
        <tgroup cols="4">
          <colspec colname="col1" colnum="1" colwidth="77*"/>
          <colspec colname="col2" colnum="2" colwidth="86*"/>
          <colspec colname="col3" colnum="3" colwidth="144*"/>
          <colspec colname="col4" colnum="4" colwidth="144*"/>
          <thead>
            <row>
              <entry colname="col1">Function Type</entry>
              <entry colname="col2">Greenplum Support</entry>
              <entry colname="col3">Description</entry>
              <entry colname="col4">Comments</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry colname="col1">IMMUTABLE</entry>
              <entry colname="col2">Yes</entry>
              <entry colname="col3">Relies only on information directly in its argument list. Given
                the same argument values, always returns the same result.</entry>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">STABLE</entry>
              <entry colname="col2">Yes, in most cases</entry>
              <entry colname="col3">Within a single table scan, returns the same result for same
                argument values, but results change across SQL statements.</entry>
              <entry colname="col4">Results depend on database lookups or parameter values.
                  <codeph>current_timestamp</codeph> family of functions is <codeph>STABLE</codeph>;
                values do not change within an execution.</entry>
            </row>
            <row>
              <entry colname="col1">VOLATILE</entry>
              <entry colname="col2">Restricted</entry>
              <entry colname="col3">Function values can change within a single table scan. For
                example: <codeph>random()</codeph>, <codeph>timeofday()</codeph>.</entry>
              <entry colname="col4">Any function with side effects is volatile, even if its result
                is predictable. For example: <codeph>setval()</codeph>.</entry>
            </row>
          </tbody>
        </tgroup>
      </table>
      <p>In Greenplum Database, data is divided up across segments — each segment is a distinct
        PostgreSQL database. To prevent inconsistent or unexpected results, do not run functions
        classified as <codeph>VOLATILE</codeph> at the segment level if they contain SQL commands or
        modify the database in any way. For example, functions such as <codeph>setval()</codeph> are
        not allowed to run on distributed data in Greenplum Database because they can cause
        inconsistent data between segment instances.</p>
      <p>To ensure data consistency, you can safely use <codeph>VOLATILE</codeph> and
          <codeph>STABLE</codeph> functions in statements that are evaluated on and run from the
        master. For example, the following statements run on the master (statements without a
          <codeph>FROM</codeph> clause):</p>
      <p>
        <codeblock>SELECT setval('myseq', 201);
SELECT foo();
</codeblock>
      </p>
      <p>If a statement has a <codeph>FROM</codeph> clause containing a distributed table <i>and</i>
        the function in the <codeph>FROM</codeph> clause returns a set of rows, the statement can
        run on the segments:</p>
      <p>
        <codeblock>SELECT * from foo();
</codeblock>
      </p>
      <p>Greenplum Database does not support functions that return a table reference
          (<codeph>rangeFuncs</codeph>) or functions that use the <codeph>refCursor</codeph>
        datatype.</p>
    </body>
  </topic>
  <topic id="topic29" xml:lang="en">
    <title id="in141007">Built-in Functions and Operators</title>
    <body>
      <p>The following table lists the categories of built-in functions and operators supported by
        PostgreSQL. All functions and operators are supported in Greenplum Database as in PostgreSQL
        with the exception of <codeph>STABLE</codeph> and <codeph>VOLATILE</codeph> functions, which
        are subject to the restrictions noted in <xref href="#topic27" format="dita"/>. See the
          <xref format="html" href="https://www.postgresql.org/docs/9.4/functions.html"
          scope="external">Functions and Operators</xref> section of the PostgreSQL documentation
        for more information about these built-in functions and operators.</p>
      <table id="in204913">
        <title>Built-in functions and operators</title>
        <tgroup cols="4">
          <colspec colname="col1" colnum="1" colwidth="129.02*"/>
          <colspec colname="col2" colnum="2" colwidth="108*"/>
          <colspec colname="col3" colnum="3" colwidth="144*"/>
          <colspec colname="col4" colnum="4" colwidth="86*"/>
          <thead>
            <row>
              <entry colname="col1">Operator/Function Category</entry>
              <entry colname="col2">VOLATILE Functions</entry>
              <entry colname="col3">STABLE Functions</entry>
              <entry colname="col4">Restrictions</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-logical.html" scope="external"
                  >Logical Operators</xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-comparison.html"
                  scope="external">Comparison Operators</xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html" href="https://www.postgresql.org/docs/9.4/functions-math.html"
                  scope="external">
                  <ph>Mathematical Functions and Operators</ph>
                </xref>
              </entry>
              <entry colname="col2">random<p>setseed</p></entry>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html" href="https://www.postgresql.org/docs/9.4/functions-string.html"
                  scope="external">
                  <ph>String Functions and Operators</ph>
                </xref>
              </entry>
              <entry colname="col2">
                <i>All built-in conversion functions</i>
              </entry>
              <entry colname="col3">convert<p>pg_client_encoding</p></entry>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-binarystring.html"
                  scope="external">
                  <ph>Binary String Functions and Operators</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-bitstring.html"
                  scope="external">
                  <ph>Bit String Functions and Operators</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-matching.html"
                  scope="external">
                  <ph>Pattern Matching</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-formatting.html"
                  scope="external">
                  <ph>Data Type Formatting Functions</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3">to_char<p>to_timestamp</p></entry>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-datetime.html"
                  scope="external">
                  <ph>Date/Time Functions and Operators</ph>
                </xref>
              </entry>
              <entry colname="col2">timeofday</entry>
              <entry colname="col3"
                  >age<p>current_date</p><p>current_time</p><p>current_timestamp</p><p>localtime</p><p>localtimestamp</p><p>now</p></entry>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html" href="https://www.postgresql.org/docs/9.4/functions-enum.html"
                  scope="external"> Enum Support Functions </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-geometry.html"
                  scope="external">
                  <ph>Geometric Functions and Operators</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html" href="https://www.postgresql.org/docs/9.4/functions-net.html"
                  scope="external">
                  <ph>Network Address Functions and Operators</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-sequence.html"
                  scope="external">
                  <ph>Sequence Manipulation Functions</ph>
                </xref>
              </entry>
              <entry colname="col2">nextval()<p>setval()</p></entry>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-conditional.html"
                  scope="external">
                  <ph>Conditional Expressions</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html" href="https://www.postgresql.org/docs/9.4/functions-array.html"
                  scope="external">
                  <ph>Array Functions and Operators</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3">
                <i>All array functions</i>
              </entry>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-aggregate.html"
                  scope="external">
                  <ph>Aggregate Functions</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-subquery.html"
                  scope="external">
                  <ph>Subquery Expressions</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/functions-comparisons.html"
                  scope="external">
                  <ph>Row and Array Comparisons</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html" href="https://www.postgresql.org/docs/9.4/functions-srf.html"
                  scope="external">
                  <ph>Set Returning Functions</ph>
                </xref>
              </entry>
              <entry colname="col2">generate_series</entry>
              <entry colname="col3"/>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html" href="https://www.postgresql.org/docs/9.4/functions-info.html"
                  scope="external">
                  <ph>System Information Functions</ph>
                </xref>
              </entry>
              <entry colname="col2"/>
              <entry colname="col3">
                <i>All session information functions</i>
                <p>
                  <i>All access privilege inquiry functions</i>
                </p><p>
                  <i>All schema visibility inquiry functions</i>
                </p><p>
                  <i>All system catalog information functions</i>
                </p><p>
                  <i>All comment information functions</i>
                </p><p>
                  <i>All transaction ids and snapshots</i>
                </p></entry>
              <entry colname="col4"/>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html" href="https://www.postgresql.org/docs/9.4/functions-admin.html"
                  scope="external">
                  <ph>System Administration Functions</ph>
                </xref>
              </entry>
              <entry colname="col2"
                  >set_config<p>pg_cancel_backend</p><p>pg_reload_conf</p><p>pg_rotate_logfile</p><p>pg_start_backup</p><p>pg_stop_backup</p><p>pg_size_pretty</p><p>pg_ls_dir</p><p>pg_read_file</p><p>pg_stat_file</p></entry>
              <entry colname="col3">current_setting<p><i>All database object size
                functions</i></p></entry>
              <entry colname="col4"><b>Note:</b> The function <codeph>pg_column_size</codeph>
                displays bytes required to store the value, possibly with TOAST compression.</entry>
            </row>
            <row>
              <entry colname="col1">
                <xref format="html" href="https://www.postgresql.org/docs/9.4/functions-xml.html"
                  scope="external">XML Functions</xref> and function-like expressions </entry>
              <entry colname="col2"/>
              <entry colname="col3">
                <p>cursor_to_xml(cursor refcursor, count int, nulls boolean, tableforest boolean,
                  targetns text)</p>
                <p>cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean,
                  targetns text)</p>
                <p>database_to_xml(nulls boolean, tableforest boolean, targetns text)</p>
                <p>database_to_xmlschema(nulls boolean, tableforest boolean, targetns text)</p>
                <p>database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns
                  text)</p>
                <p>query_to_xml(query text, nulls boolean, tableforest boolean, targetns text)</p>
                <p>query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns
                  text)</p>
                <p>query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean,
                  targetns text)</p>
                <p>schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text)</p>
                <p>schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns
                  text)</p>
                <p>schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean,
                  targetns text)</p>
                <p>table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text)</p>
                <p>table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns
                  text)</p>
                <p>table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean,
                  targetns text)</p>
                <p>xmlagg(xml)</p>
                <p>xmlconcat(xml[, ...])</p>
                <p>xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content,
                  ...])</p>
                <p>xmlexists(text, xml)</p>
                <p>xmlforest(content [AS name] [, ...])</p>
                <p>xml_is_well_formed(text)</p>
                <p>xml_is_well_formed_document(text)</p>
                <p>xml_is_well_formed_content(text)</p>
                <p>xmlparse ( { DOCUMENT | CONTENT } value)</p>
                <p>xpath(text, xml)</p>
                <p>xpath(text, xml, text[])</p>
                <p>xpath_exists(text, xml)</p>
                <p>xpath_exists(text, xml, text[])</p>
                <p>xmlpi(name target [, content])</p>
                <p>xmlroot(xml, version text | no value [, standalone yes|no|no value])</p>
                <p>xmlserialize ( { DOCUMENT | CONTENT } value AS type )</p>
                <p>xml(text)</p>
                <p>text(xml)</p>
                <p>xmlcomment(xml)</p>
                <p>xmlconcat2(xml, xml)</p>
              </entry>
              <entry colname="col4"/>
            </row>
          </tbody>
        </tgroup>
      </table>
    </body>
  </topic>
  <topic id="topic_gn4_x3w_mq">
    <title>JSON Functions and Operators</title>
    <body>
      <p>Greenplum Database includes built-in functions and operators that create and manipulate
        JSON data.<ul id="ul_ypx_f4w_2z">
          <li><xref href="#topic_o5y_14w_2z" format="dita"/></li>
          <li><xref href="#topic_u4s_wnw_2z" format="dita"/></li>
          <li><xref href="#topic_rvp_lk3_sfb" format="dita"/></li>
          <li><xref href="#topic_z5d_snw_2z" format="dita"/></li>
        </ul></p>
      <note>For <codeph>json</codeph> data type values, all key/value pairs are kept even if a JSON
        object contains duplicate keys. For duplicate keys, JSON processing functions consider the
        last value as the operative one. For the <codeph>jsonb</codeph> data type, duplicate object
        keys are not kept. If the input includes duplicate keys, only the last value is kept. See
          <xref href="../admin_guide/query/topics/json-data.html#topic_upc_tcs_fz" format="html" scope="external">About JSON Data</xref><ph
          otherprops="op-print"> in the <cite>Greenplum Database Administrator
        Guide</cite></ph>.</note>
    </body>
    <topic id="topic_o5y_14w_2z">
      <title>JSON Operators</title>
      <body>
        <p>This table describes the operators that are available for use with the
            <codeph>json</codeph> and <codeph>jsonb</codeph> data types.</p>
        <table id="table_e41_y3w_mq">
          <title><varname>json</varname> and <codeph>jsonb</codeph> Operators</title>
          <tgroup cols="5">
            <colspec colnum="1" colname="col1" colwidth="1*"/>
            <colspec colnum="2" colname="col2" colwidth="1.17*"/>
            <colspec colnum="3" colname="col3" colwidth="2.79*"/>
            <colspec colnum="4" colname="col4" colwidth="2.78*"/>
            <colspec colnum="5" colname="col5" colwidth="1.92*"/>
            <thead>
              <row>
                <entry>Operator</entry>
                <entry>Right Operand Type</entry>
                <entry>Description</entry>
                <entry>Example</entry>
                <entry>Example Result</entry>
              </row>
            </thead>
            <tbody>
              <row>
                <entry>
                  <codeph>-></codeph>
                </entry>
                <entry>
                  <codeph>int</codeph>
                </entry>
                <entry>Get the JSON array element (indexed from zero).</entry>
                <entry>
                  <codeph>'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2</codeph>
                </entry>
                <entry>
                  <codeph>{"c":"baz"}</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>-></codeph>
                </entry>
                <entry>
                  <codeph>text</codeph>
                </entry>
                <entry>Get the JSON object field by key.</entry>
                <entry>
                  <codeph>'{"a": {"b":"foo"}}'::json->'a'</codeph>
                </entry>
                <entry>
                  <codeph>{"b":"foo"}</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>->></codeph>
                </entry>
                <entry>
                  <codeph>int</codeph>
                </entry>
                <entry>Get the JSON array element as <codeph>text</codeph>.</entry>
                <entry>
                  <codeph>'[1,2,3]'::json->>2</codeph>
                </entry>
                <entry>
                  <codeph>3</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>->></codeph>
                </entry>
                <entry>
                  <codeph>text</codeph>
                </entry>
                <entry>Get the JSON object field as <codeph>text</codeph>.</entry>
                <entry>
                  <codeph>'{"a":1,"b":2}'::json->>'b'</codeph>
                </entry>
                <entry>
                  <codeph>2</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>#></codeph>
                </entry>
                <entry>
                  <codeph>text[]</codeph>
                </entry>
                <entry>Get the JSON object at specified path.</entry>
                <entry><codeph>'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}</codeph>'</entry>
                <entry>
                  <codeph>{"c": "foo"}</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>#>></codeph>
                </entry>
                <entry>
                  <codeph>text[]</codeph>
                </entry>
                <entry>Get the JSON object at specified path as <codeph>text</codeph>.</entry>
                <entry>
                  <codeph>'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'</codeph>
                </entry>
                <entry>
                  <codeph>3</codeph>
                </entry>
              </row>
            </tbody>
          </tgroup>
        </table>
        <note>There are parallel variants of these operators for both the <codeph>json</codeph> and
            <codeph>jsonb</codeph> data types. The field, element, and path extraction operators
          return the same data type as their left-hand input (either <codeph>json</codeph> or
            <codeph>jsonb</codeph>), except for those specified as returning <codeph>text</codeph>,
          which coerce the value to <codeph>text</codeph>. The field, element, and path extraction
          operators return <codeph>NULL</codeph>, rather than failing, if the JSON input does not
          have the right structure to match the request; for example if no such element
          exists.</note>
        <p>Operators that require the <codeph>jsonb</codeph> data type as the left operand are
          described in the following table. Many of these operators can be indexed by
            <codeph>jsonb</codeph> operator classes. For a full description of
            <codeph>jsonb</codeph> containment and existence semantics, see <xref
            href="../admin_guide/query/topics/json-data.html#topic_isx_2tw_mq" format="html" scope="external">jsonb Containment and Existence</xref><ph
            otherprops="op-print"> in the <cite>Greenplum Database Administrator Guide</cite></ph>.
          For information about how these operators can be used to effectively index
            <codeph>jsonb</codeph> data, see <xref
            href="../admin_guide/query/topics/json-data.html#topic_aqt_1tw_mq" format="html" scope="external">jsonb Indexing</xref><ph
            otherprops="op-print"> in the <cite>Greenplum Database Administrator
          Guide</cite></ph>.</p>
        <table id="table_dcb_y3w_mq">
          <title><codeph>jsonb</codeph> Operators</title>
          <tgroup cols="4">
            <colspec colnum="1" colname="col1" colwidth="1*"/>
            <colspec colnum="2" colname="col2" colwidth="1.94*"/>
            <colspec colnum="3" colname="col3" colwidth="4.59*"/>
            <colspec colnum="4" colname="col4" colwidth="2.5*"/>
            <thead>
              <row>
                <entry>Operator</entry>
                <entry>Right Operand Type</entry>
                <entry>Description</entry>
                <entry>Example</entry>
              </row>
            </thead>
            <tbody>
              <row>
                <entry>
                  <codeph>@></codeph>
                </entry>
                <entry>
                  <codeph>jsonb</codeph>
                </entry>
                <entry>Does the left JSON value contain within it the right value?</entry>
                <entry>
                  <codeph>'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>&lt;@</codeph>
                </entry>
                <entry>
                  <codeph>jsonb</codeph>
                </entry>
                <entry>Is the left JSON value contained within the right value?</entry>
                <entry>
                  <codeph>'{"b":2}'::jsonb &lt;@ '{"a":1, "b":2}'::jsonb</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>?</codeph>
                </entry>
                <entry>
                  <codeph>text</codeph>
                </entry>
                <entry>Does the key/element string exist within the JSON value?</entry>
                <entry>
                  <codeph>'{"a":1, "b":2}'::jsonb ? 'b'</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>?|</codeph>
                </entry>
                <entry>
                  <codeph>text[]</codeph>
                </entry>
                <entry>Do any of these key/element strings exist?</entry>
                <entry>
                  <codeph>'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>?&amp;</codeph>
                </entry>
                <entry>
                  <codeph>text[]</codeph>
                </entry>
                <entry>Do all of these key/element strings exist?</entry>
                <entry>
                  <codeph>'["a", "b"]'::jsonb ?&amp; array['a', 'b']</codeph>
                </entry>
              </row>
            </tbody>
          </tgroup>
        </table>
        <p>The standard comparison operators in the following table are available only for the
            <codeph>jsonb</codeph> data type, not for the <codeph>json</codeph> data type. They
          follow the ordering rules for B-tree operations described in <xref
            href="../admin_guide/query/topics/json-data.html#topic_aqt_1tw_mq" format="html" scope="external">jsonb Indexing</xref><ph
            otherprops="op-print"> in the <cite>Greenplum Database Administrator
          Guide</cite></ph>.</p>
        <table id="table_j4w_z5n_vq">
          <title><codeph>jsonb</codeph> Comparison Operators</title>
          <tgroup cols="2">
            <colspec colnum="1" colname="col1"/>
            <colspec colnum="2" colname="col2"/>
            <thead>
              <row>
                <entry>Operator</entry>
                <entry>Description</entry>
              </row>
            </thead>
            <tbody>
              <row>
                <entry>
                  <codeph>&lt;</codeph>
                </entry>
                <entry>less than</entry>
              </row>
              <row>
                <entry>
                  <codeph>></codeph>
                </entry>
                <entry>greater than</entry>
              </row>
              <row>
                <entry>
                  <codeph>&lt;=</codeph>
                </entry>
                <entry>less than or equal to</entry>
              </row>
              <row>
                <entry>
                  <codeph>>=</codeph>
                </entry>
                <entry>greater than or equal to</entry>
              </row>
              <row>
                <entry>
                  <codeph>=</codeph>
                </entry>
                <entry>equal</entry>
              </row>
              <row>
                <entry><codeph>&lt;></codeph> or <codeph>!=</codeph></entry>
                <entry>not equal</entry>
              </row>
            </tbody>
          </tgroup>
        </table>
        <note>The <codeph>!=</codeph> operator is converted to <codeph>&lt;></codeph> in the parser
          stage. It is not possible to implement <codeph>!=</codeph> and <codeph>&lt;></codeph>
          operators that do different things.</note>
      </body>
    </topic>
    <topic id="topic_u4s_wnw_2z">
      <title>JSON Creation Functions</title>
      <body>
        <p>This table describes the functions that create <codeph>json</codeph> data type values.
          (Currently, there are no equivalent functions for <codeph>jsonb</codeph>, but you can cast
          the result of one of these functions to <codeph>jsonb</codeph>.)</p>
        <table id="table_sqb_y3w_mb">
          <title>JSON Creation Functions </title>
          <tgroup cols="4">
            <colspec colnum="1" colname="col1"/>
            <colspec colnum="2" colname="col2"/>
            <colspec colnum="3" colname="col3"/>
            <colspec colnum="4" colname="col4"/>
            <thead>
              <row>
                <entry>Function</entry>
                <entry>Description</entry>
                <entry>Example</entry>
                <entry>Example Result</entry>
              </row>
            </thead>
            <tbody>
              <row>
                <entry>
                  <codeph>to_json(anyelement)</codeph>
                </entry>
                <entry>Returns the value as a JSON object. Arrays and composites are processed
                  recursively and are converted to arrays and objects. If the input contains a cast
                  from the type to <codeph>json</codeph>, the cast function is used to perform the
                  conversion; otherwise, a JSON scalar value is produced. For any scalar type other
                  than a number, a Boolean, or a null value, the text representation will be used,
                  properly quoted and escaped so that it is a valid JSON string.</entry>
                <entry>
                  <codeph>to_json('Fred said "Hi."'::text)</codeph>
                </entry>
                <entry>
                  <codeph>"Fred said \"Hi.\""</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>array_to_json(anyarray [, pretty_bool])</codeph>
                </entry>
                <entry>Returns the array as a JSON array. A multidimensional array becomes a JSON
                  array of arrays. <p>Line feeds will be added between dimension-1 elements if
                      <codeph>pretty_bool</codeph> is true.</p></entry>
                <entry>
                  <codeph>array_to_json('{{1,5},{99,100}}'::int[])</codeph>
                </entry>
                <entry>
                  <codeph>[[1,5],[99,100]]</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>row_to_json(record [, pretty_bool])</codeph>
                </entry>
                <entry>Returns the row as a JSON object. <p>Line feeds will be added between level-1
                    elements if <codeph>pretty_bool</codeph> is true.</p></entry>
                <entry>
                  <codeph>row_to_json(row(1,'foo'))</codeph>
                </entry>
                <entry>
                  <codeph>{"f1":1,"f2":"foo"}</codeph>
                </entry>
              </row>
              <row>
                <entry><codeph>json_build_array(VARIADIC "any"</codeph>)</entry>
                <entry>Builds a possibly-heterogeneously-typed JSON array out of a
                    <codeph>VARIADIC</codeph> argument list.</entry>
                <entry>
                  <codeph>json_build_array(1,2,'3',4,5)</codeph>
                </entry>
                <entry>
                  <codeph>[1, 2, "3", 4, 5]</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>json_build_object(VARIADIC "any")</codeph>
                </entry>
                <entry>Builds a JSON object out of a <codeph>VARIADIC</codeph> argument list. The
                  argument list is taken in order and converted to a set of key/value pairs.</entry>
                <entry>
                  <codeph>json_build_object('foo',1,'bar',2)</codeph>
                </entry>
                <entry>
                  <codeph>{"foo": 1, "bar": 2}</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>json_object(text[])</codeph>
                </entry>
                <entry>Builds a JSON object out of a text array. The array must be either a one or a
                  two dimensional array.<p>The one dimensional array must have an even number of
                    elements. The elements are taken as key/value pairs. </p><p>For a two
                    dimensional array, each inner array must have exactly two elements, which are
                    taken as a key/value pair.</p></entry>
                <entry>
                  <p>
                    <codeph>json_object('{a, 1, b, "def", c, 3.5}')</codeph>
                  </p>
                  <p>
                    <codeph>json_object('{{a, 1},{b, "def"},{c, 3.5}}')</codeph>
                  </p>
                </entry>
                <entry>
                  <codeph>{"a": "1", "b": "def", "c": "3.5"}</codeph>
                </entry>
              </row>
              <row>
                <entry>
                  <codeph>json_object(keys text[], values text[])</codeph>
                </entry>
                <entry>Builds a JSON object out of a text array. This form of
                    <codeph>json_object</codeph> takes keys and values pairwise from two separate
                  arrays. In all other respects it is identical to the one-argument form.</entry>
                <entry>
                  <codeph>json_object('{a, b}', '{1,2}')</codeph>
                </entry>
                <entry>
                  <codeph>{"a": "1", "b": "2"}</codeph>
                </entry>
              </row>
            </tbody>
          </tgroup>
        </table>
        <note><codeph>array_to_json</codeph> and <codeph>row_to_json</codeph> have the same behavior
          as <codeph>to_json</codeph> except for offering a pretty-printing option. The behavior
          described for <codeph>to_json</codeph> likewise applies to each individual value converted
          by the other JSON creation functions.</note>
        <note>The <xref href="modules/hstore.xml">hstore</xref> extension has a cast from
            <codeph>hstore</codeph> to <codeph>json</codeph>, so that <codeph>hstore</codeph> values
          converted via the JSON creation functions will be represented as JSON objects, not as
          primitive string values.</note>
      </body>
    </topic>
    <topic id="topic_rvp_lk3_sfb">
      <title>JSON Aggregate Functions</title>
      <body>
        <p>This table shows the functions aggregate records to an array of JSON objects and pairs of
          values to a JSON object </p>
        <table id="table_kkr_mk3_sfb">
          <title>JSON Aggregate Functions</title>
          <tgroup cols="4">
            <colspec colnum="1" colname="col1"/>
            <colspec colnum="2" colname="col2"/>
            <colspec colnum="3" colname="col3"/>
            <colspec colnum="4" colname="col4"/>
            <thead>
              <row>
                <entry>Function</entry>
                <entry>Argument Types</entry>
                <entry>Return Type</entry>
                <entry>Description</entry>
              </row>
            </thead>
            <tbody>
              <row>
                <entry>
                  <codeph>json_agg(record)</codeph>
                </entry>
                <entry>
                  <codeph>record</codeph>
                </entry>
                <entry>
                  <codeph>json</codeph>
                </entry>
                <entry>Aggregates records as a JSON array of objects.</entry>
              </row>
              <row>
                <entry>
                  <codeph>json_object_agg(name, value)</codeph>
                </entry>
                <entry>
                  <codeph>("any", "any")</codeph>
                </entry>
                <entry>
                  <codeph>json</codeph>
                </entry>
                <entry>Aggregates name/value pairs as a JSON object.</entry>
              </row>
            </tbody>
          </tgroup>
        </table>
      </body>
    </topic>
    <topic id="topic_z5d_snw_2z">
      <title>JSON Processing Functions</title>
      <body>
        <p>This table shows the functions that are available for processing <codeph>json</codeph>
          and <codeph>jsonb</codeph> values.</p>
        <p>Many of these processing functions and operators convert Unicode escapes in JSON strings
          to the appropriate single character. This is a not an issue if the input data type is
            <codeph>jsonb</codeph>, because the conversion was already done. However, for
            <codeph>json</codeph> data type input, this might result in an error being thrown. See
            <xref href="../admin_guide/query/topics/json-data.html#topic_upc_tcs_fz" format="html" scope="external">About JSON Data</xref><ph
            otherprops="op-print"> in the <cite>Greenplum Database Administrator
          Guide</cite></ph>.</p>
        <table id="table_wfc_y3w_mb">
          <title>JSON Processing Functions</title>
          <tgroup cols="5">
            <colspec colnum="1" colname="col1" colwidth="1.08*"/>
            <colspec colnum="2" colname="col2" colwidth="1*"/>
            <colspec colnum="3" colname="col3" colwidth="1.01*"/>
            <colspec colnum="4" colname="col4" colwidth="1.24*"/>
            <colspec colnum="5" colname="col5" colwidth="1.01*"/>
            <thead>
              <row>
                <entry>Function</entry>
                <entry>Return Type</entry>
                <entry>Description</entry>
                <entry>Example</entry>
                <entry>Example Result</entry>
              </row>
            </thead>
            <tbody>
              <row>
                <entry>
                  <codeph>json_array_length(json)</codeph>
                  <p>
                    <codeph>jsonb_array_length(jsonb)</codeph>
                  </p></entry>
                <entry><codeph>int</codeph>
                </entry>
                <entry>Returns the number of elements in the outermost JSON array.</entry>
                <entry><codeph>json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</codeph>
                </entry>
                <entry>
                  <codeph>5</codeph>
                </entry>
              </row>
              <row>
                <entry><codeph>json_each(json)</codeph>
                  <p><codeph>jsonb_each(jsonb)</codeph>
                  </p></entry>
                <entry><codeph>setof key text, value json</codeph>
                  <p><codeph>setof key text, value jsonb</codeph>
                  </p>
                </entry>
                <entry>Expands the outermost JSON object into a set of key/value pairs.</entry>
                <entry><codeph>select * from json_each('{"a":"foo", "b":"bar"}')</codeph>
                </entry>
                <entry>
                  <pre> key | value
-----+-------
 a   | "foo"
 b   | "bar"
</pre>
                </entry>
              </row>
              <row>
                <entry><codeph>json_each_text(json)</codeph>
                  <p><codeph>jsonb_each_text(jsonb)</codeph>
                  </p>
                </entry>
                <entry><codeph>setof key text, value text</codeph>
                </entry>
                <entry>Expands the outermost JSON object into a set of key/value pairs. The returned
                  values will be of type <codeph>text</codeph>.</entry>
                <entry><codeph>select * from json_each_text('{"a":"foo", "b":"bar"}')</codeph>
                </entry>
                <entry>
                  <pre> key | value
-----+-------
 a   | foo
 b   | bar
</pre>
                </entry>
              </row>
              <row>
                <entry><codeph>json_extract_path(from_json json, VARIADIC path_elems
                    text[])</codeph>
                  <p><codeph>jsonb_extract_path(from_json jsonb, VARIADIC path_elems
                      text[])</codeph>
                  </p></entry>
                <entry>
                  <p><codeph>json</codeph>
                  </p>
                  <p><codeph>jsonb</codeph>
                  </p>
                </entry>
                <entry>Returns the JSON value pointed to by <codeph>path_elems</codeph> (equivalent
                  to <codeph>#></codeph> operator).</entry>
                <entry><codeph>json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')</codeph>
                </entry>
                <entry>
                  <codeph>{"f5":99,"f6":"foo"}</codeph>
                </entry>
              </row>
              <row>
                <entry><codeph>json_extract_path_text(from_json json, VARIADIC path_elems
                    text[])</codeph>
                  <p><codeph>jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems
                      text[])</codeph>
                  </p></entry>
                <entry><codeph>text</codeph>
                </entry>
                <entry>Returns the JSON value pointed to by <codeph>path_elems</codeph> as text.
                  Equivalent to <codeph>#>></codeph> operator.</entry>
                <entry><codeph>json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4',
                    'f6')</codeph>
                </entry>
                <entry>
                  <codeph>foo</codeph>
                </entry>
              </row>
              <row>
                <entry><codeph>json_object_keys(json)</codeph>
                  <p><codeph>jsonb_object_keys(jsonb)</codeph>
                  </p></entry>
                <entry><codeph>setof text</codeph>
                </entry>
                <entry>Returns set of keys in the outermost JSON object.</entry>
                <entry><codeph>json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</codeph>
                </entry>
                <entry>
                  <pre> json_object_keys
------------------
 f1
 f2
</pre>
                </entry>
              </row>
              <row>
                <entry><codeph>json_populate_record(base anyelement, from_json
                      json)</codeph><p><codeph>jsonb_populate_record(base anyelement, from_json
                      jsonb)</codeph>
                  </p></entry>
                <entry><codeph>anyelement</codeph>
                </entry>
                <entry>Expands the object in <codeph>from_json</codeph> to a row whose columns match
                  the record type defined by base. See <xref href="#topic_z5d_snw_2z/json_proc_1"
                    format="dita">Note 1</xref>.</entry>
                <entry><codeph>select * from json_populate_record(null::myrowtype,
                    '{"a":1,"b":2}')</codeph>
                </entry>
                <entry>
                  <pre> a | b
---+---
 1 | 2
</pre>
                </entry>
              </row>
              <row>
                <entry><codeph>json_populate_recordset(base anyelement, from_json json)</codeph>
                  <p><codeph>jsonb_populate_recordset(base anyelement, from_json jsonb)</codeph>
                  </p></entry>
                <entry><codeph>setof anyelement</codeph>
                </entry>
                <entry>Expands the outermost array of objects in <codeph>from_json</codeph> to a set
                  of rows whose columns match the record type defined by base. See <xref
                    href="#topic_z5d_snw_2z/json_proc_1" format="dita">Note 1</xref>.</entry>
                <entry><codeph>select * from json_populate_recordset(null::myrowtype,
                    '[{"a":1,"b":2},{"a":3,"b":4}]')</codeph>
                </entry>
                <entry>
                  <pre> a | b
---+---
 1 | 2
 3 | 4
</pre>
                </entry>
              </row>
              <row>
                <entry><codeph>json_array_elements(json)</codeph>
                  <p><codeph>jsonb_array_elements(jsonb</codeph>)</p></entry>
                <entry>
                  <p><codeph>setof json</codeph>
                  </p>
                  <p><codeph>setof jsonb</codeph>
                  </p>
                </entry>
                <entry>Expands a JSON array to a set of JSON values.</entry>
                <entry><codeph>select * from json_array_elements('[1,true, [2,false]]')</codeph>
                </entry>
                <entry>
                  <pre>   value
-----------
 1
 true
 [2,false]
</pre>
                </entry>
              </row>
              <row>
                <entry><codeph>json_array_elements_text(json)</codeph>
                  <p><codeph>jsonb_array_elements_text(jsonb)</codeph>
                  </p></entry>
                <entry><codeph>setof text</codeph>
                </entry>
                <entry>Expands a JSON array to a set of <codeph>text</codeph> values.</entry>
                <entry><codeph>select * from json_array_elements_text('["foo", "bar"]')</codeph>
                </entry>
                <entry>
                  <pre>   value
-----------
 foo
 bar
</pre>
                </entry>
              </row>
              <row>
                <entry><codeph>json_typeof(json)</codeph><p><codeph>jsonb_typeof(jsonb)</codeph>
                  </p></entry>
                <entry><codeph>text</codeph>
                </entry>
                <entry>Returns the type of the outermost JSON value as a text string. Possible types
                  are <codeph>object</codeph>, <codeph>array</codeph>, <codeph>string</codeph>,
                    <codeph>number</codeph>, <codeph>boolean</codeph>, and <codeph>null</codeph>.
                  See <xref href="#topic_z5d_snw_2z/json_proc_2" format="dita">Note 2</xref></entry>
                <entry><codeph>json_typeof('-123.4')</codeph>
                </entry>
                <entry>
                  <codeph>number</codeph>
                </entry>
              </row>
              <row>
                <entry><codeph>json_to_record(json)</codeph><p><codeph>jsonb_to_record(jsonb)</codeph>
                  </p></entry>
                <entry><codeph>record</codeph>
                </entry>
                <entry>Builds an arbitrary record from a JSON object. See <xref
                    href="#topic_z5d_snw_2z/json_proc_1" format="dita">Note 1</xref>. <p>As with all
                    functions returning record, the caller must explicitly define the structure of
                    the record with an <codeph>AS</codeph> clause.</p></entry>
                <entry><codeph>select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a
                    int, b text, d text)</codeph>
                </entry>
                <entry>
                  <pre> a |    b    | d
---+---------+---
 1 | [1,2,3] |
</pre>
                </entry>
              </row>
              <row>
                <entry><codeph>json_to_recordset(json)</codeph>
                  <p><codeph>jsonb_to_recordset(jsonb)</codeph>
                  </p></entry>
                <entry><codeph>setof record</codeph>
                </entry>
                <entry>Builds an arbitrary set of records from a JSON array of objects See <xref
                    href="#topic_z5d_snw_2z/json_proc_1" format="dita">Note 1</xref>. <p>As with all
                    functions returning record, the caller must explicitly define the structure of
                    the record with an <codeph>AS</codeph> clause.</p></entry>
                <entry><codeph>select * from
                    json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b
                    text);</codeph>
                </entry>
                <entry>
                  <pre> a |  b
---+-----
 1 | foo
 2 |
</pre>
                </entry>
              </row>
            </tbody>
          </tgroup>
        </table>
        <note id="json_record">
          <ol id="ol_umc_2sg_pfb">
            <li id="json_proc_1">The examples for the functions
                <codeph>json_populate_record()</codeph>, <codeph>json_populate_recordset()</codeph>,
                <codeph>json_to_record()</codeph> and <codeph>json_to_recordset()</codeph> use
              constants. However, the typical use would be to reference a table in the
                <codeph>FROM</codeph> clause and use one of its <codeph>json</codeph> or
                <codeph>jsonb</codeph> columns as an argument to the function. The extracted key
              values can then be referenced in other parts of the query. For example the value can
              be referenced in <codeph>WHERE</codeph> clauses and target lists. Extracting multiple
              values in this way can improve performance over extracting them separately with
              per-key operators.<p>JSON keys are matched to identical column names in the target row
                type. JSON type coercion for these functions might not result in desired values for
                some types. JSON fields that do not appear in the target row type will be omitted
                from the output, and target columns that do not match any JSON field will be
                  <codeph>NULL</codeph>.</p></li>
            <li id="json_proc_2">The <codeph>json_typeof</codeph> function null return value of
                <codeph>null</codeph> should not be confused with a SQL <codeph>NULL</codeph>. While
              calling <codeph>json_typeof('null'::json)</codeph> will return <codeph>null</codeph>,
              calling <codeph>json_typeof(NULL::json)</codeph> will return a SQL
                <codeph>NULL</codeph>.</li>
          </ol>
        </note>
      </body>
    </topic>
  </topic>
  <topic id="topic30" xml:lang="en">
    <title id="in179666">Window Functions</title>
    <body>
      <p>The following are Greenplum Database built-in window functions. All window functions are
          <i>immutable</i>. For more information about window functions, see "Window Expressions" in
        the <cite>Greenplum Database Administrator Guide</cite>.</p>
      <table id="in164369">
        <title>Window functions</title>
        <tgroup cols="4">
          <colspec colname="col1" colnum="1" colwidth="87.46*"/>
          <colspec colname="col2" colnum="2" colwidth="59*"/>
          <colspec colname="col3" colnum="3" colwidth="180*"/>
          <colspec colname="col4" colnum="4" colwidth="121.77*"/>
          <thead>
            <row>
              <entry colname="col1">Function</entry>
              <entry colname="col2">Return Type</entry>
              <entry colname="col3">Full Syntax</entry>
              <entry colname="col4">Description</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry colname="col1">
                <codeph>cume_dist()</codeph>
              </entry>
              <entry colname="col2">
                <codeph>double precision</codeph>
              </entry>
              <entry colname="col3">
                <codeph>CUME_DIST() OVER ( [PARTITION BY </codeph>
                <ph>expr</ph>
                <codeph>] ORDER BY </codeph>
                <ph>expr</ph>
                <codeph> )</codeph>
              </entry>
              <entry colname="col4">Calculates the cumulative distribution of a value in a group of
                values. Rows with equal values always evaluate to the same cumulative distribution
                value.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>dense_rank()</codeph>
              </entry>
              <entry colname="col2">
                <codeph>bigint</codeph>
              </entry>
              <entry colname="col3">
                <codeph>DENSE_RANK () OVER ( [PARTITION BY </codeph>
                <ph>expr</ph>
                <codeph>] ORDER BY </codeph>
                <ph>expr</ph>
                <codeph>)</codeph>
              </entry>
              <entry colname="col4">Computes the rank of a row in an ordered group of rows without
                skipping rank values. Rows with equal values are given the same rank value.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>first_value(<i>expr</i>)</codeph>
              </entry>
              <entry colname="col2">same as input <ph>expr</ph> type</entry>
              <entry colname="col3">
                <codeph>FIRST_VALUE(</codeph>
                <ph>expr</ph>
                <codeph>) OVER ( [PARTITION BY </codeph>
                <ph>expr</ph>
                <codeph>] ORDER BY </codeph>
                <ph>expr</ph>
                <codeph> [ROWS|RANGE </codeph>
                <ph>frame_expr</ph>
                <codeph>] )</codeph>
              </entry>
              <entry colname="col4">Returns the first value in an ordered set of values.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>lag(<i>expr</i> [,<i>offset</i>] [,<i>default</i>])</codeph>
              </entry>
              <entry colname="col2">same as input <i>expr</i> type</entry>
              <entry colname="col3">
                <codeph>LAG(</codeph>
                <i>expr</i>
                <codeph> [,</codeph>
                <i>offset</i>
                <codeph>] [,</codeph>
                <i>default</i>
                <codeph>]) OVER ( [PARTITION BY </codeph>
                <i>expr</i>
                <codeph>] ORDER BY </codeph>
                <i>expr</i>
                <codeph> )</codeph>
              </entry>
              <entry colname="col4">Provides access to more than one row of the same table without
                doing a self join. Given a series of rows returned from a query and a position of
                the cursor, <codeph>LAG</codeph> provides access to a row at a given physical offset
                prior to that position. The default <codeph>offset</codeph> is 1. <i>default</i>
                sets the value that is returned if the offset goes beyond the scope of the window.
                If <i>default</i> is not specified, the default value is null.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>last_value(<i>expr</i></codeph>) </entry>
              <entry colname="col2">same as input <i>expr</i> type</entry>
              <entry colname="col3">
                <codeph>LAST_VALUE(<i>expr</i>) OVER ( [PARTITION BY <i>expr</i>] ORDER BY
                    <i>expr</i> [ROWS|RANGE <i>frame_expr</i>] )</codeph>
              </entry>
              <entry colname="col4">Returns the last value in an ordered set of values.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>
                  <codeph>lead(<i>expr</i> [,<i>offset</i>] [,<i>default</i>])</codeph>
                </codeph>
              </entry>
              <entry colname="col2">same as input <i>expr</i> type</entry>
              <entry colname="col3">
                <codeph>LEAD(<i>expr </i>[,<i>offset</i>] [,<i>expr</i><i>default</i>]) OVER (
                  [PARTITION BY <i>expr</i>] ORDER BY <i>expr</i> )</codeph>
              </entry>
              <entry colname="col4">Provides access to more than one row of the same table without
                doing a self join. Given a series of rows returned from a query and a position of
                the cursor, <codeph>lead</codeph> provides access to a row at a given physical
                offset after that position. If <i>offset</i> is not specified, the default offset is
                1. <i>default</i> sets the value that is returned if the offset goes beyond the
                scope of the window. If <i>default</i> is not specified, the default value is
                null.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>ntile(<i>expr</i>)</codeph>
              </entry>
              <entry colname="col2"><codeph>bigint</codeph></entry>
              <entry colname="col3">
                <codeph>NTILE(<i>expr</i>) OVER ( [PARTITION BY <i>expr</i>] ORDER BY <i>expr</i>
                  )</codeph>
              </entry>
              <entry colname="col4">Divides an ordered data set into a number of buckets (as defined
                by <i>expr</i>) and assigns a bucket number to each row.</entry>
            </row>
            <row>
              <entry colname="col1"><codeph>percent_rank()</codeph></entry>
              <entry colname="col2">
                <codeph>double precision</codeph>
              </entry>
              <entry colname="col3">
                <codeph>PERCENT_RANK () OVER ( [PARTITION BY <i>expr</i>] ORDER BY <i>expr
                  </i>)</codeph>
              </entry>
              <entry colname="col4">Calculates the rank of a hypothetical row <codeph>R</codeph>
                minus 1, divided by 1 less than the number of rows being evaluated (within a window
                partition).</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>rank()</codeph>
              </entry>
              <entry colname="col2"><codeph>bigint</codeph></entry>
              <entry colname="col3">
                <codeph>RANK () OVER ( [PARTITION BY <i>expr</i>] ORDER BY <i>expr </i>)</codeph>
              </entry>
              <entry colname="col4">Calculates the rank of a row in an ordered group of values. Rows
                with equal values for the ranking criteria receive the same rank. The number of tied
                rows are added to the rank number to calculate the next rank value. Ranks may not be
                consecutive numbers in this case.</entry>
            </row>
            <row>
              <entry colname="col1"><codeph>row_number()</codeph></entry>
              <entry colname="col2">
                <codeph>bigint</codeph>
              </entry>
              <entry colname="col3">
                <codeph>ROW_NUMBER () OVER ( [PARTITION BY <i>expr</i>] ORDER BY <i>expr
                  </i>)</codeph>
              </entry>
              <entry colname="col4">Assigns a unique number to each row to which it is applied
                (either each row in a window partition or each row of the query).</entry>
            </row>
          </tbody>
        </tgroup>
      </table>
    </body>
  </topic>
  <topic id="topic31" xml:lang="en">
    <title id="in184703">Advanced Aggregate Functions</title>
    <body>
      <p>The following built-in advanced analytic functions are Greenplum extensions of the
        PostgreSQL database. Analytic functions are <i>immutable</i>.</p>
      <note>The Greenplum MADlib Extension for Analytics provides additional advanced functions to
        perform statistical analysis and machine learning with Greenplum Database data. See <xref
          href="../analytics/madlib.html#topic1" scope="external" format="html">MADlib Extension for
        Analytics</xref>.</note>
      <table id="in2073121">
        <title>Advanced Aggregate Functions</title>
        <tgroup cols="4">
          <colspec colname="col1" colnum="1" colwidth="102.44*"/>
          <colspec colname="col2" colnum="2" colwidth="59*"/>
          <colspec colname="col3" colnum="3" colwidth="201.99*"/>
          <colspec colname="col4" colnum="4" colwidth="128*"/>
          <thead>
            <row>
              <entry colname="col1">Function</entry>
              <entry colname="col2">Return Type</entry>
              <entry colname="col3">Full Syntax</entry>
              <entry colname="col4">Description</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry colname="col1">
                <codeph>MEDIAN (<i>expr</i>)</codeph>
              </entry>
              <entry colname="col2">
                <codeph>timestamp, timestamptz, interval, float</codeph>
              </entry>
              <entry colname="col3">
                <codeph>MEDIAN (<i>expression</i>)</codeph>
                <p>
                  <i>Example:</i>
                </p>
                <codeblock>SELECT department_id, MEDIAN(salary) 
FROM employees 
GROUP BY department_id; </codeblock>
              </entry>
              <entry colname="col4">Can take a two-dimensional array as input. Treats such arrays as
                matrices.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>PERCENTILE_CONT (<i>expr</i>) WITHIN GROUP (ORDER BY <i>expr</i>
                  [DESC/ASC])</codeph>
              </entry>
              <entry colname="col2">
                <codeph>timestamp, timestamptz, interval, float</codeph>
              </entry>
              <entry colname="col3">
                <codeph>PERCENTILE_CONT(<i>percentage</i>) WITHIN GROUP (ORDER BY
                  <i>expression</i>)</codeph>
                <p>
                  <i>Example:</i>
                </p>
                <codeblock>SELECT department_id,
PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY salary DESC)
"Median_cont"; 
FROM employees GROUP BY department_id;</codeblock>
              </entry>
              <entry colname="col4">Performs an inverse distribution function that assumes a
                continuous distribution model. It takes a percentile value and a sort specification
                and returns the same datatype as the numeric datatype of the argument. This returned
                value is a computed result after performing linear interpolation. Null are ignored
                in this calculation.</entry>
            </row>
            <row>
              <entry colname="col1"><codeph>PERCENTILE_DISC (<i>expr</i>) WITHIN GROUP (ORDER BY
                    <i>expr</i> [DESC/ASC])</codeph></entry>
              <entry colname="col2">
                <codeph>timestamp, timestamptz, interval, float</codeph>
              </entry>
              <entry colname="col3">
                <codeph>PERCENTILE_DISC(<i>percentage</i>) WITHIN GROUP (ORDER BY
                  <i>expression</i>)</codeph>
                <p>
                  <i>Example:</i>
                </p>
                <codeblock>SELECT department_id, 
PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY salary DESC)
"Median_desc"; 
FROM employees GROUP BY department_id;</codeblock>
              </entry>
              <entry colname="col4">Performs an inverse distribution function that assumes a
                discrete distribution model. It takes a percentile value and a sort specification.
                This returned value is an element from the set. Null are ignored in this
                calculation.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>sum(array[])</codeph>
              </entry>
              <entry colname="col2">
                <codeph>smallint[]int[], bigint[], float[]</codeph>
              </entry>
              <entry colname="col3">
                <codeph>sum(array[[1,2],[3,4]])</codeph>
                <p>
                  <i>Example:</i>
                </p>
                <codeblock>CREATE TABLE mymatrix (myvalue int[]);
INSERT INTO mymatrix VALUES (array[[1,2],[3,4]]);
INSERT INTO mymatrix VALUES (array[[0,1],[1,0]]);
SELECT sum(myvalue) FROM mymatrix;
 sum 
---------------
 {{1,3},{4,4}}</codeblock>
              </entry>
              <entry colname="col4">Performs matrix summation. Can take as input a two-dimensional
                array that is treated as a matrix.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>pivot_sum (label[], label, expr)</codeph>
              </entry>
              <entry colname="col2">
                <codeph>int[], bigint[], float[]</codeph>
              </entry>
              <entry colname="col3">
                <codeph>pivot_sum( array['A1','A2'], attr, value)</codeph>
              </entry>
              <entry colname="col4">A pivot aggregation using sum to resolve duplicate
                entries.</entry>
            </row>
            <row>
              <entry colname="col1">
                <codeph>unnest (array[])</codeph>
              </entry>
              <entry colname="col2">set of <codeph>anyelement</codeph></entry>
              <entry colname="col3">
                <codeph>unnest( array['one', 'row', 'per', 'item'])</codeph>
              </entry>
              <entry colname="col4">Transforms a one dimensional array into rows. Returns a set of
                  <codeph>anyelement</codeph>, a polymorphic <xref format="html"
                  href="https://www.postgresql.org/docs/9.4/datatype-pseudo.html" scope="external"
                    ><ph>pseudotype in PostgreSQL</ph></xref>.</entry>
            </row>
          </tbody>
        </tgroup>
      </table>
    </body>
  </topic>
  <topic id="topic_vpj_ss1_lfb">
    <title>Text Search Functions and Operators</title>
    <body>
      <p>The following tables summarize the functions and operators that are provided for full text
        searching. See <xref href="../admin_guide/textsearch/full-text-search.html" format="html" scope="external">Using Full Text Search</xref> for a detailed
        explanation of Greenplum Database's text search facility.</p>
      <table id="table_t3f_vs1_lfb">
        <title>Text Search Operators</title>
        <tgroup cols="4">
          <colspec colnum="1" colname="col1"/>
          <colspec colnum="2" colname="col2"/>
          <colspec colnum="3" colname="col3"/>
          <colspec colnum="4" colname="col4"/>
          <thead>
            <row>
              <entry>Operator</entry>
              <entry>Description</entry>
              <entry>Example</entry>
              <entry>Result</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry><codeph>@@</codeph></entry>
              <entry><codeph>tsvector</codeph> matches <codeph>tsquery</codeph> ?</entry>
              <entry><codeph>to_tsvector('fat cats ate rats') @@ to_tsquery('cat &amp;
                  rat')</codeph></entry>
              <entry><codeph>t</codeph></entry>
            </row>
            <row>
              <entry><codeph>@@@</codeph></entry>
              <entry>deprecated synonym for <codeph>@@</codeph></entry>
              <entry><codeph>to_tsvector('fat cats ate rats') @@@ to_tsquery('cat &amp;
                  rat')</codeph></entry>
              <entry><codeph>t</codeph></entry>
            </row>
            <row>
              <entry><codeph>||</codeph></entry>
              <entry>concatenate<codeph> tsvector</codeph>s</entry>
              <entry><codeph>'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector</codeph></entry>
              <entry><codeph>'a':1 'b':2,5 'c':3 'd':4</codeph></entry>
            </row>
            <row>
              <entry><codeph>&amp;&amp;</codeph></entry>
              <entry>AND <codeph>tsquery</codeph>s together</entry>
              <entry><codeph>'fat | rat'::tsquery &amp;&amp; 'cat'::tsquery</codeph></entry>
              <entry><codeph>( 'fat' | 'rat' ) &amp; 'cat'</codeph></entry>
            </row>
            <row>
              <entry><codeph>||</codeph></entry>
              <entry>OR <codeph>tsquery</codeph>s together</entry>
              <entry><codeph>'fat | rat'::tsquery || 'cat'::tsquery</codeph></entry>
              <entry><codeph>( 'fat' | 'rat' ) | 'cat'</codeph></entry>
            </row>
            <row>
              <entry><codeph>!!</codeph></entry>
              <entry>negate a<codeph> tsquery</codeph></entry>
              <entry><codeph>!! 'cat'::tsquery</codeph></entry>
              <entry><codeph>!'cat'</codeph></entry>
            </row>
            <row>
              <entry><codeph>@></codeph></entry>
              <entry><codeph>tsquery</codeph> contains another ?</entry>
              <entry><codeph>'cat'::tsquery @> 'cat &amp; rat'::tsquery</codeph></entry>
              <entry><codeph>f</codeph></entry>
            </row>
            <row>
              <entry><codeph>&lt;@</codeph></entry>
              <entry><codeph>tsquery</codeph> is contained in ?</entry>
              <entry><codeph>'cat'::tsquery &lt;@ 'cat &amp; rat'::tsquery</codeph></entry>
              <entry><codeph>t</codeph></entry>
            </row>
          </tbody>
        </tgroup>
      </table>
      <note>The <codeph>tsquery</codeph> containment operators consider only the lexemes listed in
        the two queries, ignoring the combining operators.</note>
      <p>In addition to the operators shown in the table, the ordinary B-tree comparison operators
        (=, &lt;, etc) are defined for types <codeph>tsvector</codeph> and <codeph>tsquery</codeph>.
        These are not very useful for text searching but allow, for example, unique indexes to be
        built on columns of these types.</p>
      <table id="table_u3f_vs1_lfb">
        <title>Text Search Functions</title>
        <tgroup cols="5">
          <colspec colnum="1" colname="col1"/>
          <colspec colnum="2" colname="col2"/>
          <colspec colnum="3" colname="col3"/>
          <colspec colnum="4" colname="col4"/>
          <colspec colnum="5" colname="col5"/>
          <thead>
            <row>
              <entry>Function</entry>
              <entry>Return Type</entry>
              <entry>Description</entry>
              <entry>Example</entry>
              <entry>Result</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry><codeph>get_current_ts_config()</codeph></entry>
              <entry>regconfig</entry>
              <entry>get default text search configuration</entry>
              <entry>get_current_ts_config()</entry>
              <entry>english</entry>
            </row>
            <row>
              <entry><codeph>length(tsvector)</codeph></entry>
              <entry>integer</entry>
              <entry>number of lexemes in tsvector</entry>
              <entry>length('fat:2,4 cat:3 rat:5A'::tsvector)</entry>
              <entry>3</entry>
            </row>
            <row>
              <entry><codeph>numnode(tsquery)</codeph></entry>
              <entry>integer</entry>
              <entry>number of lexemes plus operators in tsquery</entry>
              <entry>numnode('(fat &amp; rat) | cat'::tsquery)</entry>
              <entry>5</entry>
            </row>
            <row>
              <entry><codeph>plainto_tsquery([ config regconfig , ] querytext)</codeph></entry>
              <entry>tsquery</entry>
              <entry>produce tsquery ignoring punctuation</entry>
              <entry>plainto_tsquery('english', 'The Fat Rats')</entry>
              <entry>'fat' &amp; 'rat'</entry>
            </row>
            <row>
              <entry><codeph>querytree(query tsquery)</codeph></entry>
              <entry>text</entry>
              <entry>get indexable part of a tsquery</entry>
              <entry>querytree('foo &amp; ! bar'::tsquery)</entry>
              <entry>'foo'</entry>
            </row>
            <row>
              <entry><codeph>setweight(tsvector, "char")</codeph></entry>
              <entry>tsvector</entry>
              <entry>assign weight to each element of tsvector</entry>
              <entry>setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A')</entry>
              <entry>'cat':3A 'fat':2A,4A 'rat':5A</entry>
            </row>
            <row>
              <entry><codeph>strip(tsvector)</codeph></entry>
              <entry>tsvector</entry>
              <entry>remove positions and weights from tsvector</entry>
              <entry>strip('fat:2,4 cat:3 rat:5A'::tsvector)</entry>
              <entry>'cat' 'fat' 'rat'</entry>
            </row>
            <row>
              <entry><codeph>to_tsquery([ config regconfig , ] query text)</codeph></entry>
              <entry>tsquery</entry>
              <entry>normalize words and convert to tsquery</entry>
              <entry>to_tsquery('english', 'The &amp; Fat &amp; Rats')</entry>
              <entry>'fat' &amp; 'rat'</entry>
            </row>
            <row>
              <entry><codeph>to_tsvector([ config regconfig , ] documenttext)</codeph></entry>
              <entry>tsvector</entry>
              <entry>reduce document text to tsvector</entry>
              <entry>to_tsvector('english', 'The Fat Rats')</entry>
              <entry>'fat':2 'rat':3</entry>
            </row>
            <row>
              <entry><codeph>ts_headline([ config regconfig, ] documenttext, query tsquery [,
                  options text ])</codeph></entry>
              <entry>text</entry>
              <entry>display a query match</entry>
              <entry>ts_headline('x y z', 'z'::tsquery)</entry>
              <entry>x y &lt;b>z&lt;/b></entry>
            </row>
            <row>
              <entry><codeph>ts_rank([ weights float4[], ] vector tsvector,query tsquery [,
                  normalization integer ])</codeph></entry>
              <entry>float4</entry>
              <entry>rank document for query</entry>
              <entry>ts_rank(textsearch, query)</entry>
              <entry>0.818</entry>
            </row>
            <row>
              <entry><codeph>ts_rank_cd([ weights float4[], ] vectortsvector, query tsquery [,
                  normalizationinteger ])</codeph></entry>
              <entry>float4</entry>
              <entry>rank document for query using cover density</entry>
              <entry>ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', textsearch, query)</entry>
              <entry>2.01317</entry>
            </row>
            <row>
              <entry><codeph>ts_rewrite(query tsquery, target tsquery,substitute
                tsquery)</codeph></entry>
              <entry>tsquery</entry>
              <entry>replace target with substitute within query</entry>
              <entry>ts_rewrite('a &amp; b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery)</entry>
              <entry>'b' &amp; ( 'foo' | 'bar' )</entry>
            </row>
            <row>
              <entry><codeph>ts_rewrite(query tsquery, select text)</codeph></entry>
              <entry>tsquery</entry>
              <entry>replace using targets and substitutes from a SELECTcommand</entry>
              <entry>SELECT ts_rewrite('a &amp; b'::tsquery, 'SELECT t,s FROM aliases')</entry>
              <entry>'b' &amp; ( 'foo' | 'bar' )</entry>
            </row>
            <row>
              <entry><codeph>tsvector_update_trigger()</codeph></entry>
              <entry>trigger</entry>
              <entry>trigger function for automatic tsvector column update</entry>
              <entry>CREATE TRIGGER ... tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title,
                body)</entry>
              <entry/>
            </row>
            <row>
              <entry><codeph>tsvector_update_trigger_column()</codeph></entry>
              <entry>trigger</entry>
              <entry>trigger function for automatic tsvector column update</entry>
              <entry>CREATE TRIGGER ... tsvector_update_trigger_column(tsvcol, configcol, title,
                body)</entry>
              <entry/>
            </row>
          </tbody>
        </tgroup>
      </table>
      <note>All the text search functions that accept an optional <codeph>regconfig</codeph>
        argument will use the configuration specified by <xref
          href="config_params/guc-list.xml#default_text_search_config"/> when that argument is
        omitted.</note>
      <p>The functions in the following table are listed separately because they are not usually
        used in everyday text searching operations. They are helpful for development and debugging
        of new text search configurations.</p>
      <table id="table_v3f_vs1_lfb">
        <title>Text Search Debugging Functions</title>
        <tgroup cols="5">
          <colspec colnum="1" colname="col1"/>
          <colspec colnum="2" colname="col2"/>
          <colspec colnum="3" colname="col3"/>
          <colspec colnum="4" colname="col4"/>
          <colspec colnum="5" colname="col5"/>
          <thead>
            <row>
              <entry>Function</entry>
              <entry>Return Type</entry>
              <entry>Description</entry>
              <entry>Example</entry>
              <entry>Result</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry><codeph>ts_debug([ <i>config</i> regconfig, ] <i>document</i> text, OUT
                    <i>alias</i> text, OUT <i>description</i> text, OUT <i>token</i> text, OUT
                    <i>dictionaries</i> regdictionary[], OUT <i>dictionary</i> regdictionary, OUT
                    <i>lexemes</i> text[])</codeph></entry>
              <entry><codeph>setof record</codeph></entry>
              <entry>test a configuration</entry>
              <entry><codeph>ts_debug('english', 'The Brightest supernovaes')</codeph></entry>
              <entry><codeph>(asciiword,"Word, all ASCII",The,{english_stem},english_stem,{})
                  ...</codeph></entry>
            </row>
            <row>
              <entry><codeph>ts_lexize(<i>dict</i> regdictionary, <i>token</i>
                text)</codeph></entry>
              <entry><codeph>text[]</codeph></entry>
              <entry>test a dictionary</entry>
              <entry><codeph>ts_lexize('english_stem', 'stars')</codeph></entry>
              <entry><codeph>{star}</codeph></entry>
            </row>
            <row>
              <entry><codeph>ts_parse(<i>parser_name</i> text, <i>document</i> text, OUT
                    <i>tokid</i> integer, OUT <i>token</i> text)</codeph></entry>
              <entry><codeph>setof record</codeph></entry>
              <entry>test a parser</entry>
              <entry><codeph>ts_parse('default', 'foo - bar')</codeph></entry>
              <entry>(<codeph><codeph>1,foo) ...</codeph></codeph></entry>
            </row>
            <row>
              <entry><codeph>ts_parse(<i>parser_oid</i> oid, <i>document</i> text, OUT <i>tokid</i>
                  integer, OUT <i>token</i> text)</codeph></entry>
              <entry><codeph>setof record</codeph></entry>
              <entry>test a parser</entry>
              <entry><codeph>ts_parse(3722, 'foo - bar')</codeph></entry>
              <entry><codeph>(1,foo) ...</codeph></entry>
            </row>
            <row>
              <entry><codeph>ts_token_type(<i>parser_name</i> text, OUT <i>tokid</i> integer, OUT
                    <i>alias</i> text, OUT description text)</codeph></entry>
              <entry><codeph>setof record</codeph></entry>
              <entry>get token types defined by parser</entry>
              <entry><codeph>ts_token_type('default')</codeph></entry>
              <entry><codeph>(1,asciiword,"Word, all ASCII") ...</codeph></entry>
            </row>
            <row>
              <entry><codeph>ts_token_type(<i>parser_oid</i> oid, OUT <i>tokid</i> integer, OUT
                    <i>alias</i> text, OUT <i>description</i> text)</codeph></entry>
              <entry><codeph>setof record</codeph></entry>
              <entry>get token types defined by parser</entry>
              <entry><codeph>ts_token_type(3722)</codeph></entry>
              <entry><codeph>(1,asciiword,"Word, all ASCII") ...</codeph></entry>
            </row>
            <row>
              <entry><codeph>ts_stat(<i>sqlquery</i> text, [ <i>weights</i> text, ] OUT <i>word</i>
                  text, OUT <i>ndocinteger</i>, OUT <i>nentry</i> integer)</codeph></entry>
              <entry><codeph>setof record</codeph></entry>
              <entry>get statistics of a tsvectorcolumn</entry>
              <entry><codeph>ts_stat('SELECT vector from apod')</codeph></entry>
              <entry><codeph>(foo,10,15) ...</codeph></entry>
            </row>
          </tbody>
        </tgroup>
      </table>
    </body>
  </topic>
  <topic xml:lang="en-us" id="functions-range">
    <title>Range Functions and Operators</title>
    <body>
      <p> See <xref href="datatype-range.xml#rangetypes"/> for an overview of range types. </p>
      <p> The following table shows the operators available for range types. </p>
      <table id="range-operators-table">
        <title>Range Operators</title>
        <tgroup cols="4">
          <thead>
            <row>
              <entry>Operator</entry>
              <entry>Description</entry>
              <entry>Example</entry>
              <entry>Result</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry>
                <codeph>=</codeph>
              </entry>
              <entry>equal</entry>
              <entry>
                <codeph>int4range(1,5) = '[1,4]'::int4range</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&lt;&gt;</codeph>
              </entry>
              <entry>not equal</entry>
              <entry>
                <codeph>numrange(1.1,2.2) &lt;&gt; numrange(1.1,2.3)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&lt;</codeph>
              </entry>
              <entry>less than</entry>
              <entry>
                <codeph>int4range(1,10) &lt; int4range(2,3)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&gt;</codeph>
              </entry>
              <entry>greater than</entry>
              <entry>
                <codeph>int4range(1,10) &gt; int4range(1,5)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&lt;=</codeph>
              </entry>
              <entry>less than or equal</entry>
              <entry>
                <codeph>numrange(1.1,2.2) &lt;= numrange(1.1,2.2)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&gt;=</codeph>
              </entry>
              <entry>greater than or equal</entry>
              <entry>
                <codeph>numrange(1.1,2.2) &gt;= numrange(1.1,2.0)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>@&gt;</codeph>
              </entry>
              <entry>contains range</entry>
              <entry>
                <codeph>int4range(2,4) @&gt; int4range(2,3)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>@&gt;</codeph>
              </entry>
              <entry>contains element</entry>
              <entry>
                <codeph>'[2011-01-01,2011-03-01)'::tsrange @&gt; '2011-01-10'::timestamp</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&lt;@</codeph>
              </entry>
              <entry>range is contained by</entry>
              <entry>
                <codeph>int4range(2,4) &lt;@ int4range(1,7)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&lt;@</codeph>
              </entry>
              <entry>element is contained by</entry>
              <entry>
                <codeph>42 &lt;@ int4range(1,7)</codeph>
              </entry>
              <entry>
                <codeph>f</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&amp;&amp;</codeph>
              </entry>
              <entry>overlap (have points in common)</entry>
              <entry>
                <codeph>int8range(3,7) &amp;&amp; int8range(4,12)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&lt;&lt;</codeph>
              </entry>
              <entry>strictly left of</entry>
              <entry>
                <codeph>int8range(1,10) &lt;&lt; int8range(100,110)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&gt;&gt;</codeph>
              </entry>
              <entry>strictly right of</entry>
              <entry>
                <codeph>int8range(50,60) &gt;&gt; int8range(20,30)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&amp;&lt;</codeph>
              </entry>
              <entry>does not extend to the right of</entry>
              <entry>
                <codeph>int8range(1,20) &amp;&lt; int8range(18,20)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>&amp;&gt;</codeph>
              </entry>
              <entry>does not extend to the left of</entry>
              <entry>
                <codeph>int8range(7,20) &amp;&gt; int8range(5,10)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>-|-</codeph>
              </entry>
              <entry>is adjacent to</entry>
              <entry>
                <codeph>numrange(1.1,2.2) -|- numrange(2.2,3.3)</codeph>
              </entry>
              <entry>
                <codeph>t</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>+</codeph>
              </entry>
              <entry>union</entry>
              <entry>
                <codeph>numrange(5,15) + numrange(10,20)</codeph>
              </entry>
              <entry>
                <codeph>[5,20)</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>*</codeph>
              </entry>
              <entry>intersection</entry>
              <entry>
                <codeph>int8range(5,15) * int8range(10,20)</codeph>
              </entry>
              <entry>
                <codeph>[10,15)</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>-</codeph>
              </entry>
              <entry>difference</entry>
              <entry>
                <codeph>int8range(5,15) - int8range(10,20)</codeph>
              </entry>
              <entry>
                <codeph>[5,10)</codeph>
              </entry>
            </row>
          </tbody>
        </tgroup>
      </table>
      <p> The simple comparison operators <codeph>&lt;</codeph>, <codeph>&gt;</codeph>,
          <codeph>&lt;=</codeph>, and <codeph>&gt;=</codeph> compare the lower bounds first, and
        only if those are equal, compare the upper bounds. These comparisons are not usually very
        useful for ranges, but are provided to allow B-tree indexes to be constructed on ranges. </p>
      <p> The left-of/right-of/adjacent operators always return false when an empty range is
        involved; that is, an empty range is not considered to be either before or after any other
        range. </p>
      <p> The union and difference operators will fail if the resulting range would need to contain
        two disjoint sub-ranges, as such a range cannot be represented. </p>
      <p> The following table shows the functions available for use with range types. </p>
      <table id="range-functions-table">
        <title>Range Functions</title>
        <tgroup cols="5">
          <thead>
            <row>
              <entry>Function</entry>
              <entry>Return Type</entry>
              <entry>Description</entry>
              <entry>Example</entry>
              <entry>Result</entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry>
                <codeph>
                  <codeph>lower</codeph>(<codeph>anyrange</codeph>) </codeph>
              </entry>
              <entry>range's element type</entry>
              <entry>lower bound of range</entry>
              <entry>
                <codeph>lower(numrange(1.1,2.2))</codeph>
              </entry>
              <entry>
                <codeph>1.1</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>
                  <codeph>upper</codeph>(<codeph>anyrange</codeph>) </codeph>
              </entry>
              <entry>range's element type</entry>
              <entry>upper bound of range</entry>
              <entry>
                <codeph>upper(numrange(1.1,2.2))</codeph>
              </entry>
              <entry>
                <codeph>2.2</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>
                  <codeph>isempty</codeph>(<codeph>anyrange</codeph>) </codeph>
              </entry>
              <entry>
                <codeph>boolean</codeph>
              </entry>
              <entry>is the range empty?</entry>
              <entry>
                <codeph>isempty(numrange(1.1,2.2))</codeph>
              </entry>
              <entry>
                <codeph>false</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>
                  <codeph>lower_inc</codeph>(<codeph>anyrange</codeph>) </codeph>
              </entry>
              <entry>
                <codeph>boolean</codeph>
              </entry>
              <entry>is the lower bound inclusive?</entry>
              <entry>
                <codeph>lower_inc(numrange(1.1,2.2))</codeph>
              </entry>
              <entry>
                <codeph>true</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>
                  <codeph>upper_inc</codeph>(<codeph>anyrange</codeph>) </codeph>
              </entry>
              <entry>
                <codeph>boolean</codeph>
              </entry>
              <entry>is the upper bound inclusive?</entry>
              <entry>
                <codeph>upper_inc(numrange(1.1,2.2))</codeph>
              </entry>
              <entry>
                <codeph>false</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>
                  <codeph>lower_inf</codeph>(<codeph>anyrange</codeph>) </codeph>
              </entry>
              <entry>
                <codeph>boolean</codeph>
              </entry>
              <entry>is the lower bound infinite?</entry>
              <entry>
                <codeph>lower_inf('(,)'::daterange)</codeph>
              </entry>
              <entry>
                <codeph>true</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>
                  <codeph>upper_inf</codeph>(<codeph>anyrange</codeph>) </codeph>
              </entry>
              <entry>
                <codeph>boolean</codeph>
              </entry>
              <entry>is the upper bound infinite?</entry>
              <entry>
                <codeph>upper_inf('(,)'::daterange)</codeph>
              </entry>
              <entry>
                <codeph>true</codeph>
              </entry>
            </row>
            <row>
              <entry>
                <codeph>
                  <codeph>range_merge</codeph>(<codeph>anyrange</codeph>, <codeph>anyrange</codeph>)
                </codeph>
              </entry>
              <entry>
                <codeph>anyrange</codeph>
              </entry>
              <entry>the smallest range which includes both of the given ranges</entry>
              <entry>
                <codeph>range_merge('[1,2)'::int4range, '[3,4)'::int4range)</codeph>
              </entry>
              <entry>
                <codeph>[1,4)</codeph>
              </entry>
            </row>
          </tbody>
        </tgroup>
      </table>
      <p> The <codeph>lower</codeph> and <codeph>upper</codeph> functions return null if the range
        is empty or the requested bound is infinite. The <codeph>lower_inc</codeph>,
          <codeph>upper_inc</codeph>, <codeph>lower_inf</codeph>, and <codeph>upper_inf</codeph>
        functions all return false for an empty range. </p>
    </body>
  </topic>
</topic>
